﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using Medianamik.Core.DAL.SubSonicDAL;
using Medianamik.Core.Interfaces;
using Medianamik.Core.Sugar;
using SubSonic;

namespace Medianamik.Core.DAL
{
    internal class SubsonicNodeDALProvider : INodeDALProvider
    {
        public int GetNodeCount(IQueryDefinition pQueryDef)
        {
            var command = new GetNodeCountCommandBuilder(SubSonicDAL.Node.Schema.Provider).Build(pQueryDef);
            return (int)DataService.ExecuteScalar(command);
        }

        public IEnumerable<Node> GetNodes(IQueryDefinition pQueryDef)
        {
            return GetNodes(pQueryDef, false);
        }

        public IEnumerable<Node> GetNodes(IQueryDefinition pQueryDef, bool withDeleted)
        {
            return pQueryDef.LazyLoading
                       ? GetNodesLazy(pQueryDef, withDeleted)
                       : GetNodesEager(pQueryDef, withDeleted);
        }

        private IEnumerable<Node> GetNodesLazy(IQueryDefinition pQueryDef, bool withDeleted)
        {
            if (pQueryDef.PropertyWheres.Any() && !pQueryDef.NodeWheres.Any(p => p.MemberInfo.Name.EqualsIgnoreCase("NodeTypeID")))
                throw new NotImplementedException("Node query with property wheres must specify node type for performance technicalities.");

            var command = new GetNodesCommandBuilder(SubSonicDAL.Node.Schema.Provider)
                .Build(pQueryDef, true, withDeleted);
            var nodes = new Dictionary<Guid, Node>();

            using (var reader = DataService.GetReader(command))
            {
                var factory = new SubsonicDALReaderNodeFactory();

                while (reader.Read())
                {
                    var node = factory.GetNode(reader);
                    nodes.Add(node.ID, node);
                }

                reader.NextResult();

                while (reader.Read())
                {
                    //new NodeInstance(ni.NodeInstanceId, nodes.Find(n => ni.NodeId == n.ID), ni.CultureName, ni.IsActive, ni.CreatedOn ));
                    var node = nodes[(Guid)reader["NodeId"]];
                    var cultureName = (string)reader["CultureName"];
                    var isActive = (bool)reader["IsActive"];
                    var createdOn = (DateTime)reader["CreatedOn"];
                    var nodeInstId = (Guid)reader["NodeInstanceId"];
                    var name = reader["Name"] as string;
                    var hasModifications = (bool)reader["HasModifications"];
                    int approbationLevel = 0;
                    if (reader.HasColumn(SubSonicDAL.NodeInstance.Columns.ApprobationLevel))
                    {
                        if (reader[SubSonicDAL.NodeInstance.Columns.ApprobationLevel] != DBNull.Value)
                        approbationLevel = (int)reader[SubSonicDAL.NodeInstance.Columns.ApprobationLevel];
                    }
                    var instance = new NodeInstance(nodeInstId, node, cultureName, isActive, name, hasModifications, createdOn, approbationLevel);

                    node.InternalInstances.Add(instance);
                }
            }

            return nodes.Values;
        }

        private IEnumerable<Node> GetNodesEager(IQueryDefinition pQueryDef, bool withDeleted)
        {
            throw new NotImplementedException("GetNodesEager");
            //var command = new GetNodesCommandBuilder(SubSonicDAL.Node.Schema.Provider)
            //    .Build(pQueryDef, false, withDeleted);

            //var nodes = new Dictionary<Guid, Node>();

            //using (var reader = DataService.GetReader(command))
            //{
            //    var nodeFactory = new SubsonicDALReaderNodeFactory();

            //    while (reader.Read())
            //    {
            //        var node = nodeFactory.GetNode(reader);
            //        nodes.Add(node.ID, node);
            //    }

            //    reader.NextResult();

            //    while (reader.Read())
            //    {
            //        nodes[(Guid)reader["NodeId"]].ChildNodeCount = (int)reader["ChildNodeCount"];
            //    }

            //    reader.NextResult();

            //    while (reader.Read())
            //    {
            //        //new NodeInstance(ni.NodeInstanceId, nodes.Find(n => ni.NodeId == n.ID), ni.CultureName, ni.IsActive, ni.CreatedOn ));
            //        var node = nodes[(Guid)reader["NodeId"]];
            //        var cultureName = (string)reader["CultureName"];
            //        var isActive = (bool)reader["IsActive"];
            //        var createdOn = (DateTime)reader["CreatedOn"];
            //        var nodeInstId = (Guid)reader["NodeInstanceId"];
            //        var name = reader["Name"] as string;
            //        var hasModifications = (bool)reader["HasModifications"];
            //        var instance = new NodeInstance(nodeInstId, node, cultureName, isActive, name, hasModifications,
            //                                        createdOn);
            //        node.InternalInstances.Add(instance);
            //    }

            //    reader.NextResult();
            //    var properties = new Dictionary<Node, List<NodePropertyValueLite>>();

            //    while (reader.Read())
            //    {
            //        var node = nodes[(Guid)reader["NodeId"]];
            //        if (!properties.ContainsKey(node))
            //            properties[node] = new List<NodePropertyValueLite>();
            //        properties[node].Add(new NodePropertyValueLite(reader));
            //    }

            //    var loader = new NodePropertyLoader();

            //    foreach (var propGroup in properties)
            //    {
            //        var type = propGroup.Key.NodeType;
            //        if (type == null)
            //        {
            //            throw new CoreException("Type doesn't exist");
            //        }
            //        var props = loader.LoadNodePropertyValues(propGroup.Key, propGroup.Value).ToList();
            //        propGroup.Key.NodePropertiesDictionary.Add(DateTime.MaxValue, props);
            //    }
            //}

            //return nodes.Values;
        }

        public List<Node> GetLegitimateParentNodes(Guid pTypeID)
        {
            var rules = new HierarchicalNodeRuleFullCollection();
            rules.Where(HierarchicalNodeRuleFull.Columns.ChildTypeId, pTypeID);
            rules.Load();

            var legitimateParentNodes = new List<Node>();

            legitimateParentNodes.AddRange(rules.Select(r =>
                                                        NodeFromDALHierarchicalNodeRule(r.NodeId.GetValueOrDefault(),
                                                                                        r.TypeId.GetValueOrDefault(),
                                                                                        r.ParentNodeId, r.Path,
                                                                                        r.Depth.GetValueOrDefault(),
                                                                                        r.Position.GetValueOrDefault(),
                                                                                        r.CreatedOn.GetValueOrDefault()
                                                                                        , r.EditedOn, r.EditedBy,
                                                                                        r.IsDeleted.GetValueOrDefault())));


            return legitimateParentNodes;
        }

        public void TransacSave(IEnumerable<NodeDataShell> nodeDataShells)
        {
            var userName = Utility.GetCurrentUserName();
            var transaction = new Transaction(SubSonicDAL.Node.Schema.Provider);
            var revisions = new List<NodeRevision>();
            var now = DateTime.Now;

            foreach (var nodeDataShell in nodeDataShells)
            {
                if (nodeDataShell.Node.Status == EntityStates.Deleted)
                {
                    transaction.AddRange(GetDeleteNodeCommands(nodeDataShell.Node.ID, nodeDataShell.Node.TypeId, false));
                }
                else
                {
                    var nodeDirty = nodeDataShell.Properties.Any(p => p.IsNew || p.IsDirty);
                    SetLoggingFields(nodeDataShell.Node, userName, nodeDirty, now);
                    var manyToManyNodes = nodeDataShell.ManyToManyNodes
                        .Select(n => n.InnerNode).ToList();

                    manyToManyNodes.ForEach(n => SetCreatedOn(n, userName, now));

                    AddManyToManyDeleteCommands(nodeDataShell, transaction);
                    AddInstanceDeleteCommands(nodeDataShell, transaction);
                    transaction.AddRange(manyToManyNodes.Union(new List<Node> { nodeDataShell.Node })
                        .Select(GetSaveCommand).NotNull().ToList());

                    var anyInstanceHasModifiedProperties = nodeDataShell.Instances.Any(i => i.HasModifiedProperties);
                    var anyInstanceToBePublished = nodeDataShell.Instances.Any(i => i.ToBePublished);

                    foreach (var instance in nodeDataShell.Instances)
                    {
                        var hasModifiedProperties = instance.HasModifiedProperties;
                        if (instance.ToBePublished || hasModifiedProperties || instance.IsDirty)
                        {
                            AddInstanceSaveCommand(transaction, userName, instance);

                            var draftRevision = GetDraftRevision(instance, false);

                            if (hasModifiedProperties)
                            {
                                revisions.Add(draftRevision);
                            }

                            if (instance.ToBePublished)
                            {
                                var publishedRevision = GetPublishedRevision(now, instance, draftRevision);
                                revisions.Add(publishedRevision);
                            }
                        }
                    }

                    if (!nodeDataShell.Node.IsLanguageNeutral && (anyInstanceHasModifiedProperties || anyInstanceToBePublished))
                    {
                        var draftRevision = GetDraftRevision(nodeDataShell.Instances.First(), CultureInfo.InvariantCulture.Name, false);
                        if (anyInstanceHasModifiedProperties)
                        {
                            revisions.Add(draftRevision);
                        }
                        if (anyInstanceToBePublished)
                        {
                            var publishedRevision = GetPublishedRevision(now, nodeDataShell.Instances.First(), draftRevision);
                            revisions.Add(publishedRevision);
                        }
                    }
                }
            }

            if (revisions.Count > 0)
            {
                transaction.AddRange(revisions.Select(r => r.GetSaveCommand(userName)).NotNull());
            }

            if (transaction.HasCommands)
            {
                transaction.Execute();
            }
        }

        private NodeRevision GetPublishedRevision(DateTime now, NodeInstance instance, NodeRevision draftRevision)
        {
            var publishedRevision = draftRevision.Clone();
            publishedRevision.NodeRevisionId = Guid.NewGuid();
            publishedRevision.PublishedOn = now;

            return publishedRevision;
        }

        private NodeRevision GetDraftRevision(NodeInstance instance)
        {
            return GetDraftRevision(instance, true);
        }

        private NodeRevision GetDraftRevision(NodeInstance instance, bool loadManyToMany)
        {
            return GetDraftRevision(instance, instance.Culture.Name, loadManyToMany);
        }

        private NodeRevision GetDraftRevision(NodeInstance instance, string cultureName)
        {
            return GetDraftRevision(instance, cultureName, true);
        }

        private NodeRevision GetDraftRevision(NodeInstance instance, string cultureName, bool loadManyToMany)
        {
            var draftRevision = GetNodeRevision(instance.Node, null, cultureName);

            if (draftRevision == null)
            {
                draftRevision = new NodeRevision();
                draftRevision.NodeId = instance.Node.ID;
            }

            var properties = new HashSet<NodePropertyValueLite>();
            var nonGeneratedproperties = new HashSet<NodePropertyValueLite>();

            foreach (var property in instance.Properties)
            {
                if (loadManyToMany || property.GetType() != typeof(ManyToManyNodeProperty))
                {
                    if (property.NodeTypeProperty.IsGenerated)
                    {
                        properties.Add(new NodePropertyValueLite(property));
                    }
                    else
                    {
                        nonGeneratedproperties.Add(new NodePropertyValueLite(property));
                    }
                }
            }

            draftRevision.Data = properties.SerializeProperties(cultureName);
            draftRevision.DynamicData = nonGeneratedproperties.SerializeProperties(cultureName);
            draftRevision.CultureName = cultureName;

            return draftRevision;
        }

        private void AddInstanceSaveCommand(Transaction transaction, string userName, NodeInstance instance)
        {
            if (instance.ToBePublished)
            {
                instance.HasModifiedProperties = false;
            }

            var dalInstance = ToDALNodeInstance(instance);
            var saveCommand = dalInstance.GetSaveCommand(userName);

            if (saveCommand != null)
            {
                transaction.Add(saveCommand);
            }
        }

        private void AddInstanceDeleteCommands(NodeDataShell nodeDataShell, Transaction transaction)
        {
            transaction.AddRange(nodeDataShell.DeletedInstances.Select(
                i => new DeleteQuery(SubSonicDAL.NodeInstance.Schema)
                     .WHERE(SubSonicDAL.NodeInstance.Columns.NodeId, i.Node.ID)
                     .WHERE(SubSonicDAL.NodeInstance.Columns.CultureName, i.Culture.Name)
                     .BuildDeleteCommand()).NotNull());
        }

        private void AddManyToManyDeleteCommands(NodeDataShell nodeDataShell, Transaction transaction)
        {
            transaction.AddRange(nodeDataShell.DeletedManyToManyNodes.Select(n =>
             new DeleteQuery(SubSonicDAL.Node.Schema)
                 .WHERE(SubSonicDAL.Node.Columns.NodeId, n.ID).
                 BuildDeleteCommand()).NotNull());
        }

        private QueryCommand GetSaveCommand(Node n)
        {
            var command = ToDALNode(n).GetSaveCommand();

            if (n.Status == EntityStates.New)
            {
                command.Parameters.First(p => p.ParameterName.EqualsIgnoreCase("@CreatedOn")).ParameterValue =
                    n.CreatedOn;
            }

            return command;
        }

        #region Logging Fields

        private void SetLoggingFields(Node node, string userName, bool isDirty, DateTime now)
        {
            SetCreatedOn(node, userName, now);

            if (isDirty && !node.BlockDirty)
            {
                node.EditedOn = now;
                node.EditedBy = userName;
            }
        }

        private void SetCreatedOn(Node node, string userName, DateTime now)
        {
            if (node.Status == EntityStates.New)
            {
                if (node.CreatedOn == default(DateTime))
                    node.CreatedOn = now;

                if (String.IsNullOrEmpty(node.CreatedBy))
                    node.CreatedBy = userName;
            }
        }

        #endregion

        public void DeleteNodePropertyValues(Guid pNodeId, string pCultureName, DateTime? pRevision)
        {
            // TODO YF: Copier les valeurs de la dernière publications dans Draft

            var currentNode = NodeManager.GetNode(pNodeId);



            var lastRevision = new NodeRevisionCollection()
                .Where(NodeRevision.Columns.NodeId, pNodeId)
                .Where(NodeRevision.Columns.CultureName, pCultureName)
                .Where(NodeRevision.Columns.PublishedOn, Comparison.IsNot, null)
                .OrderByDesc(NodeRevision.Columns.CreatedOn).Load().FirstOrDefault();
            var draftRevision = new NodeRevisionCollection()
                .Where(NodeRevision.Columns.NodeId, pNodeId)
                .Where(NodeRevision.Columns.CultureName, pCultureName)
                .Where(NodeRevision.Columns.PublishedOn, Comparison.Is, null).Load().FirstOrDefault();

            if (lastRevision != null)
            {
                draftRevision.Data = lastRevision.Data;
                draftRevision.Save();
            }
            // HasModification = 0
            //var deleteQuery = new SqlQuery
            //                      {
            //                          QueryCommandType = SqlQuery.QueryType.Delete
            //                      };

            //deleteQuery.From<NodePropertyValue>()
            //    .Where(NodePropertyValue.Columns.NodeId).IsEqualTo(pNodeId)
            //    .And(NodePropertyValue.Columns.CultureName).IsEqualTo(pCultureName);
            //if (pRevision.HasValue)
            //{
            //    deleteQuery.And(NodePropertyValue.Columns.Revision).IsEqualTo(pRevision.Value);
            //}
            //else
            //{
            //    deleteQuery.And(NodePropertyValue.Columns.Revision).IsNull();
            //}
            //deleteQuery.Execute();
        }

        public void SetPosition(Guid pNodeId, int pPosition)
        {
            var q = SubSonicDAL.Node.Query()
                .WHERE(SubSonicDAL.Node.Columns.NodeId, pNodeId.ToString())
                .AddUpdateSetting(SubSonicDAL.Node.Columns.Position, pPosition);

            q.QueryType = QueryType.Update;

            q.Execute();
        }

        public void DeleteNodeInstance(Guid pNodeID, string pCultureName, bool pDestroy)
        {
            if (pDestroy)
            {
                var commands = new QueryCommandCollection();

                var q = SubSonicDAL.NodeInstance.Query();
                q.QueryType = QueryType.Delete;
                q.WHERE(SubSonicDAL.NodeInstance.Columns.NodeId, pNodeID);
                q.WHERE(SubSonicDAL.NodeInstance.Columns.CultureName, pCultureName);

                commands.Add(q.BuildDeleteCommand());

                DataService.ExecuteTransaction(commands);
            }
            else
            {
                throw new NotImplementedException("IsDeleted columns is missing on NodeInstance");
            }
        }

        public void DeleteNode(Guid pNodeID, Guid nodeTypeID, bool pDestroy)
        {
            DataService.ExecuteTransaction(GetDeleteNodeCommands(pNodeID, nodeTypeID, pDestroy));
        }

        private static QueryCommandCollection GetDeleteNodeCommands(Guid pNodeID, Guid nodeTypeID, bool pDestroy)
        {
            var commands = new QueryCommandCollection();

            //Effacer les noeuds pour les relations many-to-many
            /*
            var manyToManyValueCollection = new ManyToManyValueCollection();
            manyToManyValueCollection.Where(ManyToManyValue.Columns.LeftPrimaryNodeId, pNodeID);
            manyToManyValueCollection.Load();
            */

            var nodeType = NodeTypeManager.GetNodeType(nodeTypeID);
            var node = NodeManager.GetNode(pNodeID);

            List<Guid> foreignNodeIdList = new List<Guid>();
            // Aller chercher toutes les relations Many to Many de ce type
            foreach (ManyToManyRelation relation in nodeType.ManyToManyRelations)
            {
                if (relation.LeftRelation.ForeignTypeIsManyToMany)
                {
                    // Aller chercher la propriété correspondante
                    var manyToManyNodeProperty = new ManyToManyNodeProperty(node, node.CultureName, relation);
                    // Aller chercher les noeuds qui représentent les relations
                    foreignNodeIdList.AddRange(manyToManyNodeProperty.GetCurrentRelationsValues().Select(r => r.ID));
                }
            }

            // Effacer les relations associées à ce noeud
            foreach (var item in foreignNodeIdList)
            {
                var query = new DeleteQuery(SubSonicDAL.Node.Schema);
                commands.Add(query.WHERE(SubSonicDAL.Node.Columns.NodeId, item).BuildCommand());
            }

            // Effacer le noeud
            if (pDestroy)
            {
                var query = new DeleteQuery(SubSonicDAL.Node.Schema);
                commands.Add(query.WHERE(SubSonicDAL.Node.Columns.NodeId, pNodeID).BuildCommand());
            }
            else
            {
                var query = new UpdateQuery(SubSonicDAL.Node.Schema);
                commands.Add(
                    query.AddUpdateSetting(SubSonicDAL.Node.Columns.IsDeleted, true).WHERE(
                        SubSonicDAL.Node.Columns.NodeId, pNodeID).BuildCommand());
            }

            // Mettre dans l'historique de suppression
            var sqlHistoryPattern =
                @"INSERT INTO medianamik_DeleteHistory (NodeId,TypeId)
                                    VALUES ('{0}','{1}')";
            commands.Add(new QueryCommand(string.Format(sqlHistoryPattern, pNodeID, nodeTypeID),
                SubSonicDAL.Node.Schema.Provider.Name));

            return commands;
        }

        public Publication CreatePublication(DateTime pStartDate, DateTime? pEndDate)
        {
            //Create a new Publication
            var publicationID = Guid.NewGuid();
            var newPublication = new SubSonicDAL.Publication
                                     {
                                         PublicationId = publicationID,
                                         DateStartOfPublication = pStartDate,
                                         DateEndOfPublication = pEndDate
                                     };
            newPublication.Save(Utility.GetCurrentUserName());

            //Reload to get Revision Number
            var publication = new SubSonicDAL.Publication(publicationID);

            return FromDALPublication(publication);
        }

        public Dictionary<Guid, int> ChildNodeCount(Guid[] pNodeIDs)
        {
            var dico = new Dictionary<Guid, int>();

            if (pNodeIDs.Length < 0)
            {
                return dico;
            }

            foreach (var id in pNodeIDs.Distinct())
            {
                dico.Add(id, 0);
            }
            var query = new Query(SubSonicDAL.Node.Schema);
            query.WHERE(SubSonicDAL.Node.Columns.IsDeleted, false);
            query.WHERE(SubSonicDAL.Node.Columns.IsSubEntity, false);
            query.IN(SubSonicDAL.Node.Columns.ParentNodeId, pNodeIDs.Select(id => (object)id.ToString()).ToArray());
            query.SelectList = SubSonicDAL.Node.Columns.ParentNodeId;

            using (var reader = query.ExecuteReader())
            {
                while (reader.Read())
                {
                    var id = (Guid)reader[SubSonicDAL.Node.Columns.ParentNodeId];
                    dico[id] += 1;
                }
            }

            return dico;
        }

        public string GetCurrentPath(Guid nodeId, Guid propertyId, string cultureName)
        {
            throw new NotImplementedException("GetCurrentPath");
        }

        public IEnumerable<NodeProperty> OldGetNodePropertyValues(Node node, int? pRevision)
        {
            //Récupération des Valeurs Properties
            //Aller chercher les valeurs meme si c'est une nouvelle instance
            var query = new SqlQuery()
                .From<NodePropertyValue>()
                .Where(NodePropertyValue.Columns.NodeId).IsEqualTo(node.ID.ToString());

            if (!pRevision.HasValue)
            {
                // la colonne EndRevision est toujours à NULL pour la derniere Revision
                query.And(NodePropertyValue.Columns.EndRevision).IsNull();
            }
            else
            {
                // Prendre la revision la plus recente, inferieure ou egale a la revision demandée
                query = query.And(NodePropertyValue.Columns.Revision).IsLessThanOrEqualTo(pRevision.Value)
                    .AndExpression(NodePropertyValue.Columns.EndRevision).IsGreaterThanOrEqualTo(pRevision.Value)
                    .Or(NodePropertyValue.Columns.EndRevision).IsNull()
                    .CloseExpression();
            }

            var properties = new HashSet<NodePropertyValueLite>();
            using (var reader = query.ExecuteReader())
            {
                while (reader.Read())
                {
                    var prop = new NodePropertyValueLite(reader);
                    properties.Add(prop);
                }
            }

            return new NodePropertyLoader().LoadNodePropertyValues(node, properties.ToList());
        }

        public List<NodeProperty> GetNodePropertyValues(Node node, DateTime? revision)
        {
            // Aller chercher toutes les langues
            var nodeRevisionList = GetNodeRevisionForAllCultures(node, revision);

            // Retirer les doublons de révision
            var cleanednodeRevisionList = new List<NodeRevision>();

            foreach (var nodeRevision in nodeRevisionList.Where(nodeRevision =>
                !cleanednodeRevisionList.Any(value => value.NodeId == nodeRevision.NodeId &&
                    value.CultureName == nodeRevision.CultureName)))
            {
                cleanednodeRevisionList.Add(nodeRevision);
            }

            IList<NodePropertyValueLite> propertyValueLites = new List<NodePropertyValueLite>();

            // Aller chercher toutes les propriétés
            cleanednodeRevisionList.OrderByDescending(r => r.CultureName)
                .ForEach(r =>
                             {
                                 r.Data.DeserializeProperties(node.NodeId).ApplyCulture(r.CultureName)
                                     .ForEach(propertyValueLites.Add);

                                 r.DynamicData.DeserializeProperties(node.NodeId).ApplyCulture(r.CultureName)
                                     .ForEach(propertyValueLites.Add);
                             });

            var props = new NodePropertyLoader().LoadNodePropertyValues(node, propertyValueLites).ToList();

            // Retirer les doublons de propriété
            var cleanedPropertyList = new List<NodeProperty>();

            foreach (var property in props.Where(property => !cleanedPropertyList.Any(value =>
                value.Name == property.Name && value.Culture == property.Culture)))
            {
                cleanedPropertyList.Add(property);
            }

            if (revision.HasValue)
            {
                foreach (var prop in cleanedPropertyList)
                {
                    prop.NewRevision = revision.Value;
                }
            }

            return cleanedPropertyList;
        }

        private List<NodeRevision> GetNodeRevisionForAllCultures(Node node, DateTime? revision)
        {
            if (revision.HasValue && node.IsVersioned)
                return SPs.GetLastPublishedNodeRevisions(node.NodeId, revision).ExecuteTypedList<NodeRevision>();

            var query = new Select()
            .From(NodeRevision.Schema)
            .Where(NodeRevision.Columns.NodeId).IsEqualTo(node.ID.ToString())
            .And(NodeRevision.Columns.PublishedOn).IsNull();

            return query.ExecuteAsCollection<NodeRevisionCollection>().ToList();
        }

        private NodeRevision GetNodeRevision(Node node, DateTime? revision, string cultureName)
        {
            var query = new Select()
            .Top("1")
            .From(NodeRevision.Schema)
            .Where(NodeRevision.Columns.NodeId).IsEqualTo(node.ID.ToString())
            .And(NodeRevision.Columns.CultureName).IsEqualTo(cultureName);

            if (node.IsVersioned && revision.HasValue)
            {
                query.And(NodeRevision.Columns.PublishedOn).IsLessThan(revision.Value);
            }
            else
            {
                query.And(NodeRevision.Columns.PublishedOn).IsNull();
            }

            return query.OrderDesc(NodeRevision.Columns.CreatedOn)
                .OrderDesc(NodeRevision.Columns.PublishedOn)
                .ExecuteSingle<NodeRevision>();
        }

        public IEnumerable<IRelationOption> GetRelationOptions(OneToManyRelation pRelation,
            Guid? pRootNodeId)
        {
            var optionColl = new OneToManyOptionCollection()
                .Where(OneToManyOption.Columns.ForeignPropertyid, pRelation.ForeignPropertyID)
                .Where(OneToManyOption.Columns.PrimaryTypeId, pRelation.PrimaryTypeID);

            if (pRootNodeId.HasValue)
                optionColl.Where(OneToManyOption.Columns.PrimaryNodePath, Comparison.Like,
                                 String.Format("%{0}%", pRootNodeId.Value));

            optionColl.Load();
            return optionColl.Cast<IRelationOption>();
        }

        public DateTime[] GetNodeRevisions(Guid pNodeId, string pCultureName)
        {
            var sqlQuery = new Select(NodeRevision.Columns.CreatedOn).From(NodeRevision.Schema.TableName)
                .Where(NodeRevision.Columns.NodeId).IsEqualTo(pNodeId)
                .And(NodeRevision.Columns.CultureName).IsEqualTo(pCultureName);

            return sqlQuery.OrderDesc(NodeRevision.Columns.CreatedOn)
                .ExecuteTypedList<DateTime>().ToArray();
        }

        [Obsolete("Use GetNewNodePublications instead")]
        public IEnumerable<Publication> GetNodePublications(Guid pNodeId, string pCultureName)
        {
            var publications = new PublicationCollection();
            publications.LoadAndCloseReader(SPs.GetNodePublications(pNodeId, pCultureName).GetReader());
            return publications.Select(p => FromDALPublication(p));
        }

        public List<Publication> GetNewNodePublications(Guid nodeId, string cultureName)
        {
            var publications = new Dictionary<DateTime, string>();

            var query = new Select(new[] { NodeRevision.Columns.PublishedOn, NodeRevision.Columns.CreatedBy })
                .From(NodeRevision.Schema)
                .Where(NodeRevision.Columns.NodeId).IsEqualTo(nodeId.ToString())
                .And(NodeRevision.Columns.PublishedOn).IsNotNull()
                .AndExpression(NodeRevision.Columns.CultureName).IsEqualTo(cultureName)
                    .Or(NodeRevision.Columns.CultureName).IsEqualTo("")
                .OrderDesc(NodeRevision.Columns.PublishedOn);

            using (var reader = query.ExecuteReader())
            {
                while (reader.Read())
                {
                    var date = (DateTime)reader[NodeRevision.Columns.PublishedOn];

                    if (!publications.ContainsKey(date))
                    {
                        publications.Add(date, reader[NodeRevision.Columns.CreatedBy].ToString());
                    }
                }
            }

            return publications.Select(p => new Publication(Guid.Empty, p.Key, null, 0, p.Value)).ToList();
        }

        private static Node NodeFromDALHierarchicalNodeRule(Guid pNodeId, Guid pTypeId,
            Guid? pParentNodeId, string pPath, int pDepth, short pPosition,
            DateTime pCreatedOn, DateTime? pEditedOn, string pEditedBy,
            bool isDeleted)
        {
            return new Node(pNodeId, pTypeId, pParentNodeId, pPath, pDepth, isDeleted)
               {
                   Position = pPosition,
                   CreatedOn = pCreatedOn,
                   EditedOn = pEditedOn,
                   EditedBy = pEditedBy,
                   Status = EntityStates.Loaded
               };
        }

        private static SubSonicDAL.Node ToDALNode(Node node)
        {
            var subsonicNode = node.Status == EntityStates.New
                           ? new SubSonicDAL.Node { NodeId = node.ID }
                           : new SubSonicDAL.Node(node.ID);

            if (subsonicNode.NodeId.Equals(Guid.Empty))
                subsonicNode.NodeId = node.ID;

            subsonicNode.ParentNodeId = node.ParentID;
            subsonicNode.TypeId = node.NodeTypeID;
            subsonicNode.Flags = node.Flags.Data;
            subsonicNode.CreatedOn = node.CreatedOn;
            subsonicNode.EditedOn = node.EditedOn;
            subsonicNode.EditedBy = node.EditedBy;
            subsonicNode.Position = node.Position;
            subsonicNode.IsDeleted = node.IsDeleted;
            subsonicNode.IsSubEntity = node.IsSubEntity;

            return subsonicNode;
        }

        private static SubSonicDAL.NodeInstance ToDALNodeInstance(NodeInstance pInst)
        {
            var inst = pInst.IsNew
                           ? new SubSonicDAL.NodeInstance { NodeInstanceId = pInst.InternalID }
                           : new SubSonicDAL.NodeInstance(pInst.InternalID);

            inst.CultureName = pInst.Culture.Name;
            inst.NodeId = pInst.Node.ID;
            inst.IsActive = pInst.IsActive;
            inst.Name = pInst.Name;
            inst.HasModifications = pInst.HasModifiedProperties;
            inst.ApprobationLevel = pInst.ApprobationLevel;

            return inst;
        }

        private static Publication FromDALPublication(SubSonicDAL.Publication pDALPublication)
        {
            var publication = new Publication(pDALPublication.PublicationId,
                pDALPublication.DateStartOfPublication, pDALPublication.DateEndOfPublication,
                pDALPublication.Revision, pDALPublication.CreatedBy);

            return publication;
        }
    }

    internal static class Extensions
    {
        internal static IEnumerable<T> ToEnumerable<T>(this IDataReader reader, string columnName)
        {
            try
            {
                while (reader.Read())
                {
                    yield return (T)reader[columnName];
                }
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        internal static string Merge(this IEnumerable<string> parts, char glue)
        {
            return string.Join(glue.ToString(), parts.ToArray());
        }
    }
}